﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using IHISCETeim.BLL;
using IHISCETeim.Library;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using System.Drawing;
using NPOI.SS.UserModel;
using IHISCETeim.Model;
using System.Reflection;
using System.IO;
using NPOI.SS.Util;
namespace IHISCETeim.Web.Export.MacModel
{
    public partial class ExportOUnsuitableProdManageList : ExportPageBase
    {
        protected override string xlsTemplateFileName
        {
            get
            {
                return "质管_不适合品处理票_索赔进展管控表.xls";
            }
        }
        protected override string SheetName
        {
            get
            {
                return "sheet1";
            }
        }
         /// <summary>
        /// 排序字段 值为 id
        /// </summary>
        protected string orderField
        {
            get
            {
                return GetRequestValue<string>("orderField", "StampNo");
            }
        }
        /// <summary>
        ///  排序方式 desc或 asc
        /// </summary>
        protected  string orderDirection
        {
            get
            {
                
                return GetRequestValue<string>("orderDirection", "DESC");
            }
        }
        protected override string SQLSort
        {
            get
            {
                return (orderField + " " + orderDirection);
            }
        }

        protected string StampNo
        {
            get
            {
                return GetRequestValue<string>("StampNo", "");

            }
        }
        protected string ItemCode
        {
            get
            {
                return GetRequestValue<string>("ItemCode", "");

            }
        }
        protected string Worker
        {
            get
            {
                return GetRequestValue<string>("Worker", "");

            }
        }
        protected string SerialNumber
        {
            get
            {
                return GetRequestValue<string>("SerialNumber", "");

            }
        }

        protected string SupplerName
        {
            get
            {
                return GetRequestValue<string>("SupplerName", "");
            }
        }
        protected string DownlineDateFrom
        {
            get
            {
                return GetRequestValue<string>("DownlineDateFrom", "");

            }
        }
        protected string DownlineDateTo
        {
            get
            {
                if (string.IsNullOrEmpty(DownlineDateFrom))
                {
                    return "";
                }
                else
                {
                    string todate = GetRequestValue<string>("DownlineDateTo", "");
                    if (string.IsNullOrEmpty(todate))
                        return Convert.ToDateTime(DownlineDateFrom).AddMonths(1).AddDays(-1).ToString("yyyy-MM-dd");
                    else
                        return todate;
                }

            }
        }


        protected string ExportFileName
        {
            get
            {
                return GetRequestValue<string>("ExportFileName", "");
            }
        }

        protected string commfieldsname
        {
            get
            {
                return GetRequestValue<string>("commfieldsname", "");
            }
        }
        protected string commfieldsvalue
        {
            get
            {
                return GetRequestValue<string>("commfieldsvalue", "");
            }
        }

        protected string GetWhereSql()
        {
            string wheresql = " 1=1 ";
            if (!string.IsNullOrEmpty(ItemCode))
            {
                wheresql = string.Format(" (itemcode like '%{0}%' or itemname like '%{0}%') ", ItemCode);
            }

            if (!string.IsNullOrEmpty(SerialNumber))
            {
                if (!string.IsNullOrEmpty(wheresql))
                    wheresql += string.Format(" and SerialNumber like '%{0}%'", SerialNumber);
                else
                    wheresql = string.Format(" SerialNumber like '%{0}%'", SerialNumber);
            }

            if (!string.IsNullOrEmpty(StampNo))
            {
                if (!string.IsNullOrEmpty(wheresql))
                    wheresql += string.Format(" and StampNo like'%{0}%'", StampNo);
                else
                    wheresql = string.Format(" StampNo l ike'%{0}%'", StampNo);
            }

            if (!string.IsNullOrEmpty(SupplerName))
            {
                if (!string.IsNullOrEmpty(wheresql))
                    wheresql += string.Format(" and SupplerName like '%{0}%'", SupplerName);
                else
                    wheresql = string.Format(" SupplerName like '%{0}%'", SupplerName);
            }

            string DOWNSQL = "";
            if (!string.IsNullOrEmpty(DownlineDateFrom))
            {
                DOWNSQL = QueryOperatorsEnumHelper.ConvertCommonOperator("IssueDate", QueryOperatorsEnum.范围, DownlineDateFrom + "|" + DownlineDateTo);
                if (!string.IsNullOrEmpty(wheresql))
                {
                    wheresql += " and " + DOWNSQL;
                }
                else
                {
                    wheresql = DOWNSQL;
                }
            }

            if (!string.IsNullOrEmpty(ExportFileName))
            {
                if (!string.IsNullOrEmpty(wheresql))
                    wheresql += string.Format(" and ExportFileName like'%{0}%'", ExportFileName);
                else
                    wheresql = string.Format(" ExportFileName like'%{0}%'", ExportFileName);
            }

            if (!string.IsNullOrEmpty(commfieldsname) && !string.IsNullOrEmpty(commfieldsvalue))
            {
                if (!string.IsNullOrEmpty(wheresql))
                    wheresql += string.Format(" and {0} like'%{1}%'", commfieldsname, commfieldsvalue);
                else
                    wheresql = string.Format(" {0} like'%{1}%'", commfieldsname, commfieldsvalue);
            }

            if (!string.IsNullOrEmpty(Worker))
            {
                if (!string.IsNullOrEmpty(wheresql))
                    wheresql += string.Format(" and Worker like'%{0}%'", Worker);
                else
                    wheresql = string.Format(" Worker like'%{0}%'", Worker);
            }


            return wheresql;
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            int TotalCount = 0;
            DataTable exportDT = OUnsuitableProdController.GetPagination(GetWhereSql(), 1, 999999, SQLSort, ref TotalCount);
            InitializeWorkbook();

            ISheet sheet1 = hssfworkbook.GetSheet(SheetName);
            ////写标题
            //HSSFRow rowTitle = (HSSFRow)sheet1.CreateRow(0);
            //string DTTitle = "融资租赁进度表(全部)";

            //ICell cell = rowTitle.CreateCell(0);
            //cell.SetCellValue(DTTitle);
            //ICellStyle style = hssfworkbook.CreateCellStyle();
            //style.Alignment = HorizontalAlignment.LEFT;
            //IFont font = hssfworkbook.CreateFont();
            //font.FontHeight = 20 * 20;
            //style.SetFont(font);
            //cell.CellStyle = style;

            //sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, 29));

            //FOR写入
            int rowindexBase =3;
            int rowindex = 0;
            int rowReal = rowindex + rowindexBase;
            foreach (DataRow dr in exportDT.Rows)
            {


                HSSFRow row1 = (HSSFRow)sheet1.CreateRow(rowReal);
                // row1.CreateCell(0).SetCellValue(1);
                row1.CreateCell(0).SetCellValue(dr["StampNo"].ToString());//票号NO
                row1.CreateCell(1).SetCellValue(Convert.ToDateTime(dr["IssueDate"]).ToString("yyyy-MM-dd"));//制单日期
                row1.CreateCell(2).SetCellValue( ((dr["SupplerName"].ToString().ToUpper()=="IK" && dr["LoseCtrDpt"].ToString() != "IK")?"*":"" )+   dr["SupplerName"].ToString());//供应商
                row1.CreateCell(3).SetCellValue(dr["ItemCode"].ToString());//图号

                row1.CreateCell(4).SetCellValue(dr["ItemName"].ToString());//名称
                row1.CreateCell(5).SetCellValue(Convert.ToBoolean(dr["FindPrdWorkShop"]) ? "生产车间" : "仓库");//发现场所

                row1.CreateCell(6).SetCellValue(dr["Quantity"].ToString());//数量
                row1.CreateCell(7).SetCellValue(dr["MacType"].ToString());//机型
                row1.CreateCell(8).SetCellValue(dr["IsMultiplicity"].ToString());//重复发生

                row1.CreateCell(9).SetCellValue(dr["ExportFileName"].ToString());//不适合品现象描述
                row1.CreateCell(10).SetCellValue(dr["AnalysisCause"].ToString());//原因分析
                row1.CreateCell(11).SetCellValue(dr["Countermeasure"].ToString());//处理方式

                row1.CreateCell(12).SetCellValue(GetDBDouble(dr["LoseMaterialsTotal"]));//材 料 费
                row1.CreateCell(13).SetCellValue(GetDBDouble(dr["LoseModifySubtotal"]));//委外修正费用--> LoseModifyPrice==90
               // row1.CreateCell(14).SetCellValue(Convert.ToDecimal(dr["LoseModifyPrice"]) == 80 ? GetDBDouble(dr["LoseModifySubtotal"]) : 0);//厂内修正费用

                row1.CreateCell(15).SetCellValue(GetDBDouble(dr["LoseSurveySubtotal"]));//调查费用
                row1.CreateCell(16).SetCellValue(GetDBDouble(dr["LoseSaveSubtotal"]));//保存费
                row1.CreateCell(17).SetCellValue(GetDBDouble(dr["LoseDelaySubtotal"]));//其他
                row1.CreateCell(18).SetCellFormula(string.Format("sum(M{0}:R{0})", rowReal+1));//合计

                row1.CreateCell(19).SetCellValue(GetDBDouble(dr["SubmitJinE"]));//提交金额
                row1.CreateCell(20).SetCellValue(GetDBString(dr["SubmitDate"]));//提交时间
                row1.CreateCell(21).SetCellValue(GetDBDouble(dr["ClaimRMB"]));//人民币
                row1.CreateCell(22).SetCellValue(GetDBString(dr["ClaimOpenDate"]));//开票时间
                row1.CreateCell(23).SetCellValue(GetDBString(dr["ClaimPenZenHao"]));//凭证号

                row1.CreateCell(24).SetCellFormula(string.Format("V{0}-T{0}", rowReal + 1));//索赔收入差异 人民币

                row1.CreateCell(25).SetCellValue(GetDBString(dr["ClaimAnalyze"]));//索赔差异分析
                row1.CreateCell(26).SetCellValue(GetDBString(dr["ProcessReason"]));//报废原因
                row1.CreateCell(27).SetCellValue(GetDBString(dr["ProcessDocNum"]));//单据号
                row1.CreateCell(28).SetCellValue(GetDBString(dr["ProcessVoucherNum"]));//凭证号

                rowindex++;
                rowReal = rowindex + rowindexBase;
            }

         
            //Force excel to recalculate all the formula while open
            sheet1.ForceFormulaRecalculation = true;

            WriteToFile();
        }

        protected override string GetXlsFileName(string oldFileName)
        {
            return string.Format("质量部索赔进展管控表_{0}.xls", DateTime.Now.ToString("yyyy-MM-dd"));
        }
        private Double GetDBDouble(object v)
        {
            try
            {
                if (v is DBNull)
                    return 0;
                return Convert.ToDouble(v);
            }
            catch {
                return 0;
            }
        }
        private string GetDBString(object v)
        {
            if (v is DBNull)
                return "";
            return v.ToString();
        }
    }
}